﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data; 
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Web;

namespace WebStudio.Framework.Helper
{
    public interface IDBHelper
    {
        int ExecuteNonQuery(string sql);
        IDataReader ExecuteReader(string sql);
        object ExecuteScalar(string sql);
        int ExecuteIntScalar(string sql);
        string ExecuteStringScalar(string sql);
        DataTable ExecuteDateTable(string sql);
    }

    public class SQLHelper : IDBHelper
    {
        private string connectionString;

        public SQLHelper(string connectionString)
        {
            this.connectionString = connectionString;
        }

        #region IDBHelper 成员

        public int ExecuteNonQuery(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql.Trim(), conn);
            conn.Open();
            int i = cmd.ExecuteNonQuery();
            conn.Close();

            return i;
        }

        public IDataReader ExecuteReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql.Trim(), conn);
            conn.Open();

            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public object ExecuteScalar(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql.Trim(), conn);
            conn.Open();
            object o = cmd.ExecuteScalar();
            conn.Close();

            return o;
        }

        public int ExecuteIntScalar(string sql)
        {
            object o = ExecuteScalar(sql);
            if (o != null && o.ToString() != "")
            {
                return ConvertHelper.IntParse(o);
            }
            else
                return 0;
        }

        public string ExecuteStringScalar(string sql)
        {
            return ConvertHelper.StringParse(ExecuteScalar(sql));
        }

        public DataTable ExecuteDateTable(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql.Trim(), conn);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();

            DataTable table = new DataTable();
            adapter.Fill(table);
            conn.Close();
            return table;
        }

        #endregion
    }

    public class AccessHelper : IDBHelper
    {
        private string connectionString;

        public AccessHelper(string connectionString)
        {
            this.connectionString = connectionString;
        }

        #region IDBHelper 成员

        public int ExecuteNonQuery(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand(sql.Trim(), conn);
            conn.Open();
            int i = cmd.ExecuteNonQuery();
            conn.Close();

            return i;
        }

        public IDataReader ExecuteReader(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand(sql.Trim(), conn);
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public object ExecuteScalar(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand(sql.Trim(), conn);
            conn.Open();
            object o = cmd.ExecuteScalar();
            conn.Close();

            return o;
        }

        public int ExecuteIntScalar(string sql)
        {
            return ConvertHelper.IntParse(ExecuteScalar(sql));
        }

        public string ExecuteStringScalar(string sql)
        {
            return ConvertHelper.StringParse(ExecuteScalar(sql));
        }

        public DataTable ExecuteDateTable(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand(sql.Trim(), conn);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            conn.Open();

            DataTable table = new DataTable();
            adapter.Fill(table);

            conn.Close();
            return table;
        }

        #endregion
    }

    /// <summary>
    /// 数据库操作类
    /// </summary>
    public class DBHelper
    {
        //为方便单元测试调用数据库连接字符串暂时用常量
        //string path = System.Web.HttpContext.Current.Server.MapPath("~/");
        private static readonly string connctionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~/") + "App_Data\\Data.mdb;Persist Security Info=False";
        //private static readonly string connctionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\zmgg.com\\App_Data\\Data.mdb;Persist Security Info=False";
        //WebConfigurationManager.ConnectionStrings["Default_Access"].ConnectionString;
        private static readonly string dbType ="access";
        //WebConfigurationManager.AppSettings["dbtype"];

        private static IDBHelper db;
        public static IDBHelper DB
        {
            get
            {
                if (db == null)
                {
                    if (dbType.ToLower() == "access")
                    {
                        db = new AccessHelper(connctionString);
                    }
                    else
                    {
                        db = new SQLHelper(connctionString);
                    }
                }

                return db;
            }
        }

        public static int ExecuteNonQuery(string sql)
        {
            return DB.ExecuteNonQuery(sql);
        }
        public static IDataReader ExecuteReader(string sql)
        {
            return DB.ExecuteReader(sql);
        }
        public static object ExecuteScalar(string sql)
        {
            return DB.ExecuteScalar(sql);
        }
        public static int ExecuteIntScalar(string sql)
        {
            return DB.ExecuteIntScalar(sql);
        }
        public static string ExecuteStringScalar(string sql)
        {
            return DB.ExecuteStringScalar(sql);
        }
        public static DataTable ExecuteDateTable(string sql)
        {
            return DB.ExecuteDateTable(sql);
        }
    }
}
